﻿/*
    Copyright 2013 Bob Moss (bobbyjmoss@gmail.com)

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace OpenRAMS.Model
{
    public class WorkTicketLog
    {
        public int workTicketLogLinkID { get; set; }
        public DateTime workTicketLogTime { get; set; }
        public String workTicketLogCategory { get; set; }
        public String workTicketLogCreator { get; set; }
        public String workTicketLogTitle { get; set; }
        public String workTicketLogDesc { get; set; }
        public Decimal workTicketLogTimeTakenHours { get; set; }

        //for use later when we come to verify work ticket category
        private WorkCategories wkCategories;

        //internal class variables/arrays
        protected String[] attribNames;
        protected int numAttribs;
        private bool NewOrOld;
        protected String tableName;

        //database-related variables
        private SqlConnection dbConnection;
        private SqlDataReader dbReader;
        private SqlCommand dbCommand;
        private String dbInString;

        public WorkTicketLog()
        {
            initHardCodedVals();
            NewOrOld = true;    //if true, is new record

            workTicketLogLinkID = -1;
            workTicketLogTime = Convert.ToDateTime(System.DateTime.Now.ToString());
            workTicketLogCategory = "";
            workTicketLogCreator = "";
            workTicketLogTitle = "";
            workTicketLogDesc = "";
            workTicketLogTimeTakenHours = 0;

            //reads in db connection string
            try { dbInString = ConfigurationManager.ConnectionStrings["default"].ConnectionString; }
            catch (Exception e) { throw e; }  //this will cause error conditions later

            wkCategories = new WorkCategories();
        }

        public int Load(int workTicketID, DateTime logTime)
        {
            String queryFull = "SELECT * FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + workTicketID + " AND " + attribNames[1] + "='" + logTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
            int rCheck = 0; //used for error-checking later
            String[] outputVars = new String[numAttribs + 1];

            //check workTicketID is sensible
            if (workTicketID < 0) { return -1; }

            //reinitialise work categories list (just in case)
            wkCategories = new WorkCategories();

            //build query
            queryFull += queryCondition;

            NewOrOld = false;   //importing existing record

            //initialise output array
            for (int i = 0; i < outputVars.Length; i++)
            {
                outputVars[i] = "";
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //build query string
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //Import values from query into string array
            while (dbReader.Read())
            {
                if (rCheck > 0)
                {
                    return -1;  //if more than one result was returned, run hard & run fast!
                }
                else
                {
                    int i = 0;  //iterator for output string
                    foreach (String att in attribNames)
                    {
                        try { outputVars[i] = dbReader[att].ToString(); }
                        catch { return -1; }
                        i++;
                    }
                }
                rCheck++;   //tracks number of results
            }

            //Verify that at least one result was returned
            if (rCheck < 1) { return -1; };

            //Import values from Db
            try { workTicketLogLinkID = Convert.ToInt32(outputVars[0]); }
            catch { return -1; }

            try { workTicketLogTime = DateTime.Parse(outputVars[1]); }
            catch { return -1; }

            workTicketLogCategory = outputVars[2];
            workTicketLogCreator = outputVars[3];
            workTicketLogTitle = outputVars[4];
            workTicketLogDesc = outputVars[5];

            try { workTicketLogTimeTakenHours = Convert.ToDecimal(outputVars[6]); }
            catch { return -1; }

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

           NewOrOld = false; //fail-safe
           return 0;
        }

        public int Save()
        {
            //Strings to handle query as it's built
            String queryFull = "", queryCondition = "";

            //Verify workTicketID is something sensible
            if (workTicketLogLinkID < 0) { return -1; }

            //check the work category chosen exists
            List<String> existingWorkCats = wkCategories.getCategories();
            bool testCatInput = false;
            foreach (String cat in existingWorkCats)
            {
                if (workTicketLogCategory == cat)
                {
                    testCatInput = true;
                    break;
                }
            }
            if (testCatInput == false) { return -1; }

            //likewise with the user creator record
            Users test = new Users();
            int result = test.Populate("txtUserName", "'" + workTicketLogCreator + "'");
            if (result < 0) { return -1; }

            List<UserRecord> testList = test.getUserList();
            if (testList.Count() < 1) { return -1; }

            //is this a new record? If so, we need an INSERT statement. Else, an UPDATE
            if (NewOrOld == true)
            {
                queryFull = "INSERT INTO " + tableName + " VALUES (";
                queryFull += workTicketLogLinkID + ", ";
                queryFull += "'" + workTicketLogTime.ToString("dd-MMMM-yyyy H:mm:ss") + "', ";
                queryFull += "'" + workTicketLogCategory + "', ";
                queryFull += "'" + workTicketLogCreator + "', ";
                queryFull += "'" + workTicketLogTitle + "', ";
                queryFull += "'" + workTicketLogDesc + "', ";
                queryFull += "'" + workTicketLogTimeTakenHours + "')";
            }
            else
            {
                //verify record exists
                int wtlTest = Load(workTicketLogLinkID, workTicketLogTime);
                if (wtlTest < 0) { return -1; }

                //build query
                queryFull = "UPDATE " + tableName + " SET ";
                queryCondition = "WHERE " + attribNames[0] + "=" + workTicketLogLinkID + " AND " + attribNames[1] + "='" + workTicketLogTime + "'";

                queryFull += attribNames[2] + "='" + workTicketLogCategory + "', ";
                queryFull += attribNames[3] + "='" + workTicketLogCreator + "', ";
                queryFull += attribNames[4] + "='" + workTicketLogTitle + "', ";
                queryFull += attribNames[5] + "='" + workTicketLogDesc + "', ";
                queryFull += attribNames[6] + "=" + workTicketLogTimeTakenHours + " ";
                queryFull += queryCondition;
            }

            //attempt DB connection
            try
            {
                dbConnection = new SqlConnection(dbInString);
                dbConnection.Open();
            }
            catch { return -1; }

            //create command that will run query
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //if new, check saved correctly
            if (NewOrOld == true)
            {
                //int aTest = Load(workTicketLogLinkID, workTicketLogTime);
                //if (aTest < 0) { return -1; }

                WorkTicketLogs wtList = new WorkTicketLogs();
                wtList.Populate(-1, attribNames[0] + "=" + workTicketLogLinkID + " AND CONVERT(char,"+ attribNames[1] + ",120)='" + workTicketLogTime.ToString(workTicketLogTime.ToString("yyyy-MM-dd HH:mm:ss")) + "'");
                List<WorkTicketLog> logs = wtList.getWorkTicketLogList();

                Boolean testBool = false;
                foreach (WorkTicketLog log in logs)
                {
                    if (log.workTicketLogLinkID == workTicketLogLinkID && log.workTicketLogTime == workTicketLogTime)
                    {
                        testBool = true;
                        break;
                    }
                }
                if (!testBool) { return -1; }
            }

            NewOrOld = false;   //fail-safe

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }

            return 0;
        }

        public int Delete()
        {
            String queryFull = "DELETE FROM " + tableName + " ", queryCondition = "WHERE " + attribNames[0] + "=" + workTicketLogLinkID + " AND " + attribNames[1] + "='" + workTicketLogTime + "'";

            //check workTicketID is sensible
            if (workTicketLogLinkID < 0) { return -1; }

            //check record exists in first place
            WorkTicketLog test = new WorkTicketLog();
            int recordCheck = test.Load(workTicketLogLinkID, workTicketLogTime);
            if (recordCheck < 0) { return -1; }

            //build query
            queryFull += queryCondition;
            dbCommand = dbConnection.CreateCommand();
            dbCommand.CommandText = queryFull;
            dbCommand.CommandType = CommandType.Text;
            dbCommand.CommandTimeout = 15;

            //Run command, and tidy-up if exceptions occur
            try { dbReader = dbCommand.ExecuteReader(); }
            catch
            {
                dbReader.Close();
                dbConnection.Close();
                return -1;
            }

            //check was deleted successfully
            WorkTicketLog aTest = new WorkTicketLog();
            recordCheck = aTest.Load(workTicketLogLinkID, workTicketLogTime);
            if (recordCheck > -1) { return -1; }

            NewOrOld = true;    //as the record no longer exists in the database, is considered 'new'

            //Clean-up DB connection
            try
            {
                dbReader.Close();
                dbConnection.Close();
            }
            catch { return -1; }
            return 0;
        }

        protected void initHardCodedVals()
        {
            //set hard-coded values
            numAttribs = 6;
            tableName = "tblWorkTicketLog";
            attribNames = new String[numAttribs + 1];
            attribNames[0] = "intWorkTicketID"; //primary key value
            attribNames[1] = "dtTimeStamp";
            attribNames[2] = "txtWorkCategory";
            attribNames[3] = "txtUserName";
            attribNames[4] = "txtTitle";
            attribNames[5] = "txtDescription";
            attribNames[6] = "numHours";
        }
    }
}